package com.zhq.dao.imp;

import com.zhq.bean.User;
import com.zhq.dao.BaseUserDao;
import com.zhq.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author:钟豪强
 * @Description:
 * @Date Created in  2020-08-16 18:48
 * @Modified By:
 */
public class UserDaoMysql implements BaseUserDao {
    private static String SQL_CONSOLE = " SELECT COUNT(ID) DATA_SIZE, COUNT(TO_DAYS(CREATETIME) = TO_DAYS(NOW()) OR NULL) DATA_DAY FROM USER ";
    private static String SQL_FIND_ALL = " SELECT * FROM USER ";
    private static String SQL_FIND_LIMIT = " SELECT * FROM USER LIMIT ?,? ";
    private static String SQL_FIND_BY_PHONE = " SELECT * FROM USER WHERE USERPHONE = ? ";
    private static String SQL_FIND_ORDER_BY_ALL = " SELECT COUNT(STATUS = 1 OR NULL) NUMBER, USER.USERNAME USERNAME FROM USER, EXPRESS WHERE USER.USERPHONE = EXPRESS.USERPHONE GROUP BY USERNAME ORDER BY NUMBER DESC ";
    private static String SQL_FIND_ORDER_BY_YEAR = " SELECT COUNT(STATUS = 1 AND YEAR(OUTTIME) = YEAR(NOW()) OR NULL) NUMBER, USER.USERNAME USERNAME FROM USER, EXPRESS WHERE USER.USERPHONE = EXPRESS.USERPHONE GROUP BY USERNAME ORDER BY NUMBER DESC ";
    private static String SQL_FIND_ORDER_BY_MONTH = " SELECT COUNT(STATUS = 1 AND YEAR(OUTTIME) = YEAR(NOW()) AND MONTH(OUTTIME) = MONTH(NOW()) OR NULL) NUMBER, USER.USERNAME USERNAME FROM USER, EXPRESS WHERE USER.USERPHONE = EXPRESS.USERPHONE GROUP BY USERNAME ORDER BY NUMBER DESC ";
    private static String SQL_INSERT = " INSERT INTO USER (USERNAME,PASSWORD,CREATETIME,USERNUMBER,USERPHONE) VALUES(?,?,NOW(),?,?) ";
    private static String SQL_UPDATE_LAST_LOGIN_TIME = " UPDATE USER SET LASTLOGINTIME = ? WHERE ID = ? ";
    private static String SQL_UPDATE = " UPDATE USER SET USERNAME = ?, PASSWORD = ?, USERPHONE = ?, USERNUMBER = ? WHERE ID = ? ";
    private static String SQL_UPDATE_NOT_PHONE = " UPDATE USER SET USERNAME = ?, PASSWORD = ?, USERNUMBER = ? WHERE ID = ? ";
    private static String SQL_DELETE = " DELETE FROM USER WHERE ID = ? ";

    /**
     * (没有像快递一样的两种数据类型,直接用map)
     * 用于查询用户表的用户数量(总数和新增)
     *
     * @return 返回{size:总数,day:新增},没有返回null
     */
    @Override
    public Map<String, Integer> console() {
        HashMap<String, Integer> map = new HashMap<>();
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            //3.填充参数(可选)
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取结果
            while (result.next()) {
                int data_size = result.getInt("data_size");
                int data_day = result.getInt("data_day");
                map.put("data_size",data_size);
                map.put("data_day",data_day);
            }
            return map;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 用于查询所有用户信息
     *
     * @param limit      true代表分页,false代表不分页
     * @param offset     SQL语句分页起始索引
     * @param pageNumber 页查询页数
     * @return 返回用户信息的集合, 没有信息返回null
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<User> data = new ArrayList<>();
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译
        try {
            if (limit) {
                 state = conn.prepareStatement(SQL_FIND_LIMIT);
                //3.填充参数(可选)
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取结果
            while (result.next()) {
                int id = result.getInt("id");
                String username = result.getString("username");
                String userPhone = result.getString("userphone");
                String userNumber = result.getString("usernumber");
                String password = result.getString("password");
                Timestamp createTime = result.getTimestamp("createtime");
                Timestamp lastLoginTime = result.getTimestamp("lastlogintime");
                User user = new User(id,username,userPhone,userNumber,password,createTime,lastLoginTime);
                data.add(user);
            }
            return data;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 根据用户手机号码查询用户信息(微信端)
     * @param phoneNumber   用户号码
     * @return  返回用户信息
     */
    @Override
    public User findByPhone(String phoneNumber) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            //3.填充参数(可选)
            state.setString(1,phoneNumber);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取结果
            User user = null;
            while (result.next()) {
                int id = result.getInt("id");
                String username = result.getString("username");
                String userPhone = result.getString("userphone");
                String userNumber = result.getString("usernumber");
                String password = result.getString("password");
                Timestamp createTime = result.getTimestamp("createtime");
                Timestamp lastLoginTime = result.getTimestamp("lastlogintime");
                user = new User(id,username,userPhone,userNumber,password,createTime,lastLoginTime);
            }
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }


    /**
     * 用于排序查询快递数靠前的用户
     * @param type  0表示查询总排名,1表示年排名,2表示月排名
     * @return  返回这个User只放username和expressNumber快递数(不属于数据库中user表的一部分)
     */
    @Override
    public List<User> lazyboard(int type) {
        ArrayList<User>  data = new ArrayList<>();
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.预编译
        try {
            switch (type) {
                case 0:
                    state = conn.prepareStatement(SQL_FIND_ORDER_BY_ALL);
                    break;
                case 1:
                    state = conn.prepareStatement(SQL_FIND_ORDER_BY_YEAR);
                    break;
                case 2:
                    state = conn.prepareStatement(SQL_FIND_ORDER_BY_MONTH);
                    break;
                default:
                    break;
            }
            //3.填充参数(可选)
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取结果
            while (result.next()) {
                int number = result.getInt("number");
                String username = result.getString("username");
                User user = new User(username,number);
                data.add(user);
            }
            return data;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    /**
     * 添加新用户
     *
     * @param userName   用户名
     * @param userPhone  用户手机号码
     * @param userNumber 用户身份证
     * @param password   用户密码
     * @return true代表添加成功
     */
    @Override
    public boolean insert(String userName, String userPhone, String userNumber, String password) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.填充参数
            state.setString(1,userName);
            state.setString(2,password);
            state.setString(3,userNumber);
            state.setString(4,userPhone);
            //4.执行SQL语句,获取结果
            return state.executeUpdate() > 0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用于根据id,修改用户信息
     *
     * @param id      用户id
     * @param newUser 新的用户信息
     * @return 返回true代表修改成功
     */
    @Override
    public boolean update(int id, User newUser) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            //3.    填充参数(可选)
            state.setString(1,newUser.getUsername());
            state.setString(2,newUser.getPassword());
            state.setString(3,newUser.getUserPhone());
            state.setString(4,newUser.getUserNumber());
            state.setInt(5,id);
            //4.    执行SQL语句,获取执行结果
            return state.executeUpdate() > 0?true:false;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用于根据id,修改用户信息
     *
     * @param id      用户id
     * @param newUser 新的用户信息(username,password,userNumber)
     * @return
     */
    @Override
    public boolean updateNotPhone(int id, User newUser) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE_NOT_PHONE);
            //3.    填充参数(可选)
            state.setString(1,newUser.getUsername());
            state.setString(2,newUser.getPassword());
            state.setString(3,newUser.getUserNumber());
            state.setInt(4,id);
            //4.    执行SQL语句,获取执行结果
            return state.executeUpdate() > 0?true:false;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用于根据用户id,修改上一次登录时间
     *
     * @param id 用户id
     * @return
     */
    @Override
    public boolean updateLastLoginTime(int id, Timestamp lastLoginTime) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_UPDATE_LAST_LOGIN_TIME);
            //3.填充参数
            state.setTimestamp(1,lastLoginTime);
            state.setInt(2,id);
            //4.执行SQL语句,获取结果
            return state.executeUpdate() > 0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }


    /**
     * 用于根据用户id,删除用户信息
     *
     * @param id 用户id
     * @return true代表删除成功
     */
    @Override
    public boolean delete(int id) {
        //1.获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.预编译
        try {
            state = conn.prepareStatement(SQL_DELETE);
            //3.填充参数
            state.setInt(1,id);
            //4.执行SQL语句,获取结果
            return state.executeUpdate() > 0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
